package cn.org.zx.manage.user.ctrl;

import java.io.BufferedOutputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.net.ftp.FTPClient;
import org.apache.commons.net.ftp.FTPFile;
import org.apache.commons.net.ftp.FTPReply;

import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.PaperSize;
import jxl.write.Formula;
import jxl.write.Alignment;
import jxl.write.BorderLineStyle;
import jxl.write.Colour;
import jxl.write.Label;
import jxl.write.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.multiaction.MultiActionController;

import cn.org.zx.common.aop.BeanFactory;
import cn.org.zx.common.commutils.DBTimeServer;
import cn.org.zx.common.commutils.RandomString;
import cn.org.zx.common.commutils.Json;
import cn.org.zx.common.commutils.StringUtils;
import cn.org.zx.common.log.LogWritter;
import cn.org.zx.manage.user.blh.UserBlh;
import cn.org.zx.db.dbconnection.DBProperties;

public class UserCtrl extends MultiActionController{	
	private final UserBlh blhuser;
	public FTPClient ftpClient = null;	
	public UserCtrl(){
		blhuser = (UserBlh)BeanFactory.getBean(new UserBlh());
	}

	//会员单位用户查询--用户管理模块
	public ModelAndView getUserQueryList(HttpServletRequest request,HttpServletResponse response) throws IOException {
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		Map mapData = request.getParameterMap();
		Map mapReturn = blhuser.getUserQueryList(mapData);
		return new ModelAndView("/manager/user/user_query.jsp",mapReturn);
	}
	
	
	//活动报名创建保存--活动报名模块
	public ModelAndView getUserCreateSave(HttpServletRequest request,HttpServletResponse response) throws IOException {
		System.out.println("in");
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		//		
		
		Map mapData = request.getParameterMap();
		String strIP = request.getRemoteAddr();
		Map mapReturn = blhuser.getUserCreateSave(mapData,strIP);
		
		PrintWriter out = null;
		try {
			out = response.getWriter();
			String result = Json.toJson(mapReturn);
			out.print(result);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		return null;
	}
	
	//从业人员数据导出--从业人员模块
	public ModelAndView getUserExport(HttpServletRequest request,HttpServletResponse response) throws IOException {
		String strrandom = RandomString.randomNumeric(10);
		String strunitcode = "";
		String strunitname = "";
		String strapplycode = "";
		String strapplyname = "";
		String strlistimg = "";
		String strimg1 = "";
		String strimg2 = "";		
		String strurl = DBProperties.getURL();
		
		request.setCharacterEncoding("utf-8");
		response.setCharacterEncoding("utf-8");
		response.setContentType("application/vnd.ms-excel");
		response.addHeader("Content-Disposition","attachment;filename=officedate"+strrandom+".xls");
				
		Map mapData = request.getParameterMap();
		Map mapReturn = blhuser.getUserExport(mapData);	
		List<Map> datalist = (List)mapReturn.get("datalist");
				
		try{	
			OutputStream out = response.getOutputStream();
			WritableWorkbook wb = Workbook.createWorkbook(out);
			wb.setColourRGB(Colour.BLUE,100,300,100);
			
			//设置:标题为 字体为ARIAL 粗体
			WritableFont fmtCaption = new WritableFont(WritableFont.ARIAL);
			fmtCaption.setBoldStyle(WritableFont.NO_BOLD);
			fmtCaption.setPointSize(WritableFont.DEFAULT_POINT_SIZE);
			//设置：标题 居中对齐 边框画细线
			WritableCellFormat fmtCenterCaption = new WritableCellFormat(fmtCaption);
			fmtCenterCaption.setAlignment(Alignment.CENTRE);
			fmtCenterCaption.setVerticalAlignment(VerticalAlignment.CENTRE);
			fmtCenterCaption.setWrap(true);
			fmtCenterCaption.setBorder(jxl.format.Border.TOP,BorderLineStyle.THIN);
			fmtCenterCaption.setBorder(jxl.format.Border.RIGHT,BorderLineStyle.THIN);
			fmtCenterCaption.setBorder(jxl.format.Border.BOTTOM,BorderLineStyle.THIN);		
			//设置：明细字体及对齐
			WritableFont fmtDetail = new WritableFont(WritableFont.ARIAL);
			fmtCaption.setPointSize(WritableFont.DEFAULT_POINT_SIZE);
			
			WritableCellFormat fmtCenterDetail = new WritableCellFormat(fmtDetail);
			fmtCenterDetail.setAlignment(Alignment.CENTRE);
			
			WritableSheet ws = wb.createSheet("sheet1",0);
			SheetSettings set = new SheetSettings();
			set.setPageStart(1);
			set.setPaperSize(PaperSize.A3);		
			set.setZoomFactor(50);
			set.getPaperSize();
					
			//定义列宽
			ws.setColumnView(0,7);			//序号
			ws.setColumnView(1,15);			//姓名
			ws.setColumnView(2,20);			//手机号
			ws.setColumnView(3,12);	       	//身份证号码后六位
			ws.setColumnView(4,20);			//全科合格证号
			ws.setColumnView(5,30);	       	//目前所在单位
			ws.setColumnView(6,30);	       	//是否拟申请加入中国注册会计师协会，成为执业或非执业会员
			ws.setColumnView(7,15);	       	//收件人姓名
			ws.setColumnView(8,20);	       	//收件人手机号码
			ws.setColumnView(9,50);	       	//收件人详细地址
			ws.setColumnView(10,15);	    //来自IP
			ws.setColumnView(11,30);	    //提交答卷时间
			ws.setColumnView(12,30);	    //图片1
			ws.setColumnView(13,30);	    //图片2
						
			//定义列名	
			Label lblColumn = null;
			lblColumn = new Label(0,0,"序号",fmtCenterCaption);        
			ws.addCell(lblColumn);
			lblColumn = new Label(1,0,"姓名",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(2,0,"手机号",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(3,0,"身份证号码后六位",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(4,0,"全科合格证号",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(5,0,"目前所在单位",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(6,0,"是否拟申请加入中国注册会计师协会，成为执业或非执业会员",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(7,0,"收件人姓名",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(8,0,"收件人手机号码",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(9,0,"收件人详细地址",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(10,0,"来自IP",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(11,0,"提交答卷时间",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(12,0,"图片1",fmtCenterCaption);
			ws.addCell(lblColumn);
			lblColumn = new Label(13,0,"图片2",fmtCenterCaption);
			ws.addCell(lblColumn);
									
			//写入内容
			int iRows = 1;
			if(datalist!=null){
				for(int i=0;i<datalist.size();i++){
					Map dataMap = (Map)datalist.get(i);				
					lblColumn =  new Label(0,iRows,String.valueOf(iRows),fmtCenterDetail);   
				    ws.addCell(lblColumn);
				    lblColumn =  new Label(1,iRows,StringUtils.toString(dataMap.get("self_name")),fmtCenterDetail);
					ws.addCell(lblColumn);
					lblColumn =  new Label(2,iRows,StringUtils.toString(dataMap.get("self_phone")),fmtCenterDetail);
					ws.addCell(lblColumn);
					lblColumn =  new Label(3,iRows,StringUtils.toString(dataMap.get("self_id_card")),fmtCenterDetail);
					ws.addCell(lblColumn);
					lblColumn =  new Label(4,iRows,StringUtils.toString(dataMap.get("self_general")),fmtCenterDetail);
					ws.addCell(lblColumn);
					strunitcode = StringUtils.toString(dataMap.get("self_unit"));
					if(strunitcode.equals("1")) strunitname = "会计师事务所";
					else if(strunitcode.equals("2")) strunitname = "非会计师事务所";
					else if(strunitcode.equals("3")) strunitname = "暂无工作单位";
					lblColumn =  new Label(5,iRows,strunitname,fmtCenterDetail);
					ws.addCell(lblColumn);
					strapplycode = StringUtils.toString(dataMap.get("apply"));
					if(strapplycode.equals("1")) strapplyname = "拟申请成为执业会员";
					else if(strapplycode.equals("2")) strapplyname = "拟申请成为非执业会员";
					else if(strapplycode.equals("3")) strapplyname = "暂不申请";
					lblColumn =  new Label(6,iRows,strapplyname,fmtCenterDetail);
					ws.addCell(lblColumn);					
					lblColumn =  new Label(7,iRows,StringUtils.toString(dataMap.get("consignee_name")),fmtCenterDetail);
					ws.addCell(lblColumn);
					lblColumn =  new Label(8,iRows,StringUtils.toString(dataMap.get("consignee_phone")),fmtCenterDetail);
					ws.addCell(lblColumn);	
					lblColumn =  new Label(9,iRows,StringUtils.toString(dataMap.get("consignee_address")),fmtCenterDetail);
					ws.addCell(lblColumn);
					lblColumn =  new Label(10,iRows,StringUtils.toString(dataMap.get("d_ipadress")),fmtCenterDetail);
					ws.addCell(lblColumn);
					lblColumn =  new Label(11,iRows,StringUtils.toString(dataMap.get("create_time")),fmtCenterDetail);
					ws.addCell(lblColumn);
					//处理图片
					strlistimg = StringUtils.toString(dataMap.get("img1"));
					if(strlistimg.length()>0){
						String[] arr = strlistimg.split(",");
						if(arr.length==1){
							strimg1 = strurl+arr[0];
							String formu = "HYPERLINK(\""+strimg1+"\",\"查看图片1\")";
							Formula formula = new Formula(12,iRows,formu,fmtCenterDetail);
							ws.addCell(formula);
						}
						else if(arr.length==2){
							strimg1 = strurl+arr[0];
							String formu1 = "HYPERLINK(\""+strimg1+"\",\"查看图片1\")";
							Formula formula1 = new Formula(12,iRows,formu1,fmtCenterDetail);
							ws.addCell(formula1);
							//WritableHyperlink link1 = new WritableHyperlink(12,iRows,strimg1,ws,0,0);
							//ws.addHyperlink(link1);	
							strimg2 = strurl+arr[1];
							String formu2 = "HYPERLINK(\""+strimg2+"\",\"查看图片2\")";
							Formula formula2 = new Formula(13,iRows,formu2,fmtCenterDetail);
							ws.addCell(formula2);
						}
					}					
									
				    iRows = iRows + 1;
				}
			}
							
			wb.write();
			wb.close();		
			out.close();			
		}
		catch(Exception e){   
			e.printStackTrace();
		}		
		return null;
	}
	
	//批量下载
	public ModelAndView getUserDownload(HttpServletRequest request,HttpServletResponse response) throws IOException {
		Map mapData = request.getParameterMap();
		
		List<String> lstimg = new ArrayList();
		String strfilepathlist = "";
		String strfilepath = "";
		//
		Map mapReturn = blhuser.getUserExport(mapData);	
		
		List<Map> datalist = (List)mapReturn.get("datalist");
		if(datalist!=null){
			for(int i=0;i<datalist.size();i++){
				Map dataMap = (Map)datalist.get(i);
				strfilepathlist = StringUtils.toString(dataMap.get("img1"));
				if(strfilepathlist.length()>0){
					if(strfilepathlist.indexOf(",")>0){
						String[] arr = strfilepathlist.split(",");
						for(int ii=0;ii<arr.length;ii++){
							strfilepath = arr[ii];
							if(!strfilepath.equals("")){
								strfilepath = strfilepath.replace("file/cpatrain", "");
								lstimg.add(strfilepath);
							}							
						}
					}
					else{
						strfilepath = strfilepathlist;
						strfilepath = strfilepath.replace("file/cpatrain", "");
						lstimg.add(strfilepath);
					}
				}
			}
		} 		
		
		System.out.println("lstimg11="+lstimg.size());
		downloadPictureZip(lstimg,response);
		
		return null;
	}
		
	//批量下载
	public void downloadPictureZip(List<String> img1,HttpServletResponse response) {
		String strgdate = DBTimeServer.getServerTimestamp().toString();
		String strgdatef = strgdate.substring(0, 4)+strgdate.substring(5, 7)+strgdate.substring(8, 10);
		String zipName = "B"+strgdatef+RandomString.randomNumeric(4)+".zip";
		
		ZipOutputStream zipos = null;
		DataOutputStream os = null;

		InputStream is = null;
		response.setContentType("APPLICATION/OCTET-STREAM");
		response.setHeader("Content-Disposition", "attachment; filename=" + zipName);
		try {
			zipos = new ZipOutputStream(new BufferedOutputStream(response.getOutputStream()));
            for (String fjfile : img1) {
                initFtpClient(); //链接ftp (ftp设置下次连接是需登录)
                if (!fjfile.equals("")) {                	
                    String localFilePath = fjfile.substring(0, fjfile.lastIndexOf("/"));		//截取路径                    
                    String fileName = fjfile.substring(fjfile.lastIndexOf("/") + 1);			//截取名字                   
                    String path = localFilePath.substring(localFilePath.lastIndexOf("/")+1)+"/";                    
                    ftpClient.changeWorkingDirectory(localFilePath);			//切换到目录
                    zipos.setMethod(ZipOutputStream.DEFLATED);					//设置压缩方法DEFLATED
                    FTPFile[] files = ftpClient.listFiles();                  
                    
                    for (FTPFile file : files) {
                        if (file.getName().equals(fileName)) {  				//在ftp列表判断                        	
                            zipos.putNextEntry(new ZipEntry(path+fileName));
                            os = new DataOutputStream(zipos);
                            is = ftpClient.retrieveFileStream(fileName);		//输出
                            byte[] b = new byte[100];
                            int length = 0;
                            while ((length = is.read(b)) != -1) {
                                os.write(b, 0, length);
                            }
                            is.close();
                            zipos.closeEntry();
                            ftpClient.disconnect();
                        }
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (is != null) {
                    is.close();
                }
                if (os != null) {
                    os.close();
                }
                zipos.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
	 }
	 
	//连接FTP
	public void initFtpClient() throws Exception {
		String strftphome = DBProperties.getFtpHome();
		int iftpport = Integer.parseInt(DBProperties.getFtpPort());
		String strftpuser = DBProperties.getFtpUser();
		String strftppassword = DBProperties.getFtpPassword();
				
        ftpClient = new FTPClient();
        ftpClient.setControlEncoding("UTF-8");
        ftpClient.enterLocalPassiveMode();
    	LogWritter.loginInfo("connecting...ftp服务器:"+strftphome+":"+iftpport+"");
        ftpClient.connect(strftphome, iftpport); 		//连接ftp服务器
        ftpClient.login(strftpuser, strftppassword); 	//登录ftp服务器
        int replyCode = ftpClient.getReplyCode(); 		//是否成功登录服务器
        if (!FTPReply.isPositiveCompletion(replyCode)) {
        	LogWritter.loginInfo("connect failed...ftp服务器:  登陆失败"+strftphome+":"+iftpport+"");
            throw new RuntimeException("登陆失败");
        } else {
        	LogWritter.loginInfo("connect successfu...ftp服务器: 登陆成功"+strftphome+":"+iftpport+"");
        }
	}
	//
	public static void main(String[] args) throws Exception{
		List<String> lstimg = new ArrayList();
		
		lstimg.add("/Z123456789/1611805185551_25.jpg");
		lstimg.add("/Z987456123/1611805231217_04.jpg");
		lstimg.add("/Z987456123/1611805236323_64.jpg");
		
		UserCtrl ftp = new UserCtrl();
		//ftp.initFtpClient();
	} 
}
